package com.joysuch.wwyt.quartz.createtable.handler;

import com.joysuch.wwyt.quartz.createtable.constant.CreateTableScriptConstant;
import com.joysuch.wwyt.quartz.createtable.constant.TableNamePrefixConstant;
import com.joysuch.wwyt.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StopWatch;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.Set;

/**
 * 动态建表处理器
 *
 * @author 叶君翔
 * @date 2023/07/11 10:42
 */
@Slf4j
@Component
public class CreateTableHandler {

    @Resource
    private DataSource dataSource;

    /**
     * 通用: 验证表是否存在, 不存在则创建表
     *
     * @param map 待创建的表map, key: 用来获取创建表的脚本、value: 待创建的表名
     */
    public void createTable(Map<String, String> map) {
        if (CollectionUtils.isEmpty(map)) {
            return;
        }
        StopWatch stopWatch = new StopWatch();
        Connection conn = null;
        try {
            stopWatch.start("获取数据库连接");
            conn = dataSource.getConnection();
            stopWatch.stop();
            log.info("========== {}, 耗时: {} ms", stopWatch.getLastTaskName(), stopWatch.getLastTaskTimeMillis());

            stopWatch.start("创建表");
            Set<String> keys = map.keySet();
            for (String scriptKey : keys) {
                if (StringUtil.isEmpty(scriptKey) || StringUtil.isEmpty(map.get(scriptKey))) {
                    continue;
                }
                String tableName = map.get(scriptKey);
                // 判断表是否存在
                String existSql = "SHOW TABLES IN wwyt WHERE Tables_in_wwyt = " + "'" + tableName + "'";
                PreparedStatement ps = conn.prepareStatement(existSql);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    log.info("========== {} 已存在", tableName);
                    continue;
                }
                String script = this.getCreateTableScript(scriptKey);
                if (StringUtil.isEmpty(script)) {
                    log.info("========== 建表脚本为空");
                    continue;
                }
                // 拼接建表语句
                String sql = String.format(script, tableName);
                PreparedStatement pa = conn.prepareStatement(sql);
                pa.execute();
            }
            stopWatch.stop();
            log.info("========== {}, 耗时: {} ms", stopWatch.getLastTaskName(), stopWatch.getLastTaskTimeMillis());
        } catch (Exception e) {
            log.info("========== 建表异常, {}", e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (stopWatch.isRunning()) {
                    stopWatch.stop();
                    log.info("========== {} 耗时: {} ms", stopWatch.getLastTaskName(), stopWatch.getTotalTimeMillis());
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private String getCreateTableScript(String scriptKey) {
        if (TableNamePrefixConstant.INVESTIGATE_TASK.equals(scriptKey)) {
            return CreateTableScriptConstant.INVESTIGATE_TASK;
        }
        if (TableNamePrefixConstant.INVESTIGATE_TASK_ASSIGN.equals(scriptKey)) {
            return CreateTableScriptConstant.INVESTIGATE_TASK_ASSIGN;
        }
        if (TableNamePrefixConstant.INVESTIGATE_TASK_SUMMARY.equals(scriptKey)) {
            return CreateTableScriptConstant.INVESTIGATE_TASK_SUMMARY;
        }
        if (TableNamePrefixConstant.INVESTIGATE_RECORD.endsWith(scriptKey)) {
            return CreateTableScriptConstant.INVESTIGATE_RECORD;
        }
        if (TableNamePrefixConstant.INVESTIGATE_RECORD_ITEM.endsWith(scriptKey)) {
            return CreateTableScriptConstant.INVESTIGATE_RECORD_ITEM;
        }
        return "";
    }

}
